package db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 
 * Description: 操纵数据的类，包括连接，建表，删除，更新，增加，查询记录等方法。
 * 
 * @since  2009/2/15
 * @author ashen
 * 
 */
public class Transaction {
	private static final Log log = LogFactory.getLog(Transaction.class);
	
	private Connection connection;
	private Statement statement;
	//该类的excute方法执行失败，将flag标识置为false，初始值为true
	//但需要执行commit时，若flag为true，则commit，如果flag为false，则rollback
	private boolean flag;
	
	/**
	 * 构造函数
	 * 
	 * 建立连接，获得Statement对象
	 * 
	 */
	public Transaction(){
		flag=true;
		rebuildConnection();
		rebuildStatement();
	}
	
	/**
	 * 构造函数
	 * 
	 * 建立连接，获得Statement对象
	 * 
	 * @param jndiName tomcat数据库连接池的JNDI名
	 */
	public Transaction(String jndiName) {
		flag=true;
		rebuildConnection(jndiName);
		rebuildStatement();
	}
	
	/**
	 * the method rebuildStatement of class Transaction
	 * 
	 * 获得Statement对象
	 */
	public void rebuildStatement(){
		try {
			statement =connection.createStatement();
		} catch (SQLException e) {
			log.error("db rebuildStatement error:"+e.getMessage());
		} 
	}
	
	/**
	 * the method rebuildConnection of class Transaction
	 * 
	 * 该方法用于数据库建立连接，使用tomcat数据库连接池.
	 * 
	 */
	public void rebuildConnection() {		
		DataSource ds = null;
		InitialContext ctx=null;
		try {
			ctx = new InitialContext();
			ds=(DataSource)ctx.lookup("java:comp/env/jdbc/projectDB");
			connection = ds.getConnection();
            connection.setAutoCommit(false);
            log.info("open connection.");
		} catch (NamingException e) {
			log.error("Can not find JNDI Name, datesource configuration error!");
		} catch (SQLException e) {
			log.error("Can not connect to database, please check the path and the username and the password!" + e.getMessage());
		}     
    } 
	
	/**
	 * the method rebuildConnection of class Transaction
	 * 
	 * 该方法用于数据库建立连接，使用tomcat数据库连接池，参数为JNDI名.
	 * 
	 * @param jndiName tomcat数据库连接池的JNDI名
	 */
	public void rebuildConnection(String jndiName) {
        jndiName = "";//jndiName;
        try {
            Context ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup(jndiName);
            connection = ds.getConnection();  
            connection.setAutoCommit(false);
            log.info("open connection.");
        } catch (NamingException e) {
        	log.error("Can not find JNDI Name, datesource configuration error!");
        } catch (SQLException e) {
        	log.error("Can not connect to database, please check the path and the username and the password!" + e.getMessage());
        }
    } 
	
	/**
	 * the method excute of class Transaction
	 * 
	 * 该方法执行SQL语句.
	 * 
	 * @param sqlStr SQL语句，包括插入，删除，修改操作.
	 * 
	 * @throws SQLException
	 * @return 操作是否成功
	 */
	public void excute(String sqlStr){
		System.out.println("db excute begin:");
		System.out.println(sqlStr);
		try {
			statement.execute(sqlStr);		
			log.info("db excute completed.");
		} catch (SQLException e) {
			flag=false;
			log.error("db excute error:"+e.getMessage());
		}
	}
	
	/**
	 * the method query of class Transaction
	 * 
	 * 该方法执行查询语句，用于得到多个查询结果的查询，返回查询得到的结果数组
	 * 
	 * @param sqlStr 用于查询SQL语句
	 * @return 查询得到的结果数组
	 * @throws SQLException
	 */
	public ArrayList<HashMap> query(String sqlStr){   
	    ArrayList<HashMap> resultList=getResult(sqlStr); 
        return resultList;
	} 
	
	/**
	 * the method query of class Transaction
	 * 
	 * 该方法执行查询语句，用于得到一个查询结果的查询，返回查询得到的单个结果
	 * 
	 * @param sqlStr 用于查询SQL语句
	 * @return 查询得到Hashtable类型的结果
	 */
	public HashMap queryOneResult(String sqlStr){
		ArrayList<HashMap> list=getResult(sqlStr);
		if(list.isEmpty())
			return null;
	    HashMap hashMap = list.get(0);
        return hashMap;
	} 
	
	/**
	 * the method getResult of class Transaction
	 * 
	 * 该方法用于根据查询SQL语句返回数据
	 * 
	 * @param sqlStr 用于查询SQL语句
	 * @return 将记录存储在ArrayList<Hashtable>对象中，并返回ArrayList<Hashtable>对象
	 */
	public ArrayList<HashMap> getResult(String sqlStr){ 
		log.info("db query begin:");
		log.info(sqlStr);
	    ResultSet resultSet=null;
	    ResultSetMetaData resultSetMetaData = null;
	    ArrayList<HashMap> resultList=new ArrayList<HashMap>();
	    try {   
	        resultSet = statement.executeQuery(sqlStr);  
	        resultSetMetaData=resultSet.getMetaData();
	        HashMap hashMap=null;
	        while (resultSet.next()) {  	        	
	    		hashMap=getHashMap(resultSet,resultSetMetaData);
	    		if(hashMap!=null)
	    			resultList.add(hashMap); 
	        } 
	        resultSet.close();    
	        return resultList;
	    } catch (SQLException e) {   
	    	log.error("db qurey error:"+e.getMessage()); 
	    	return resultList;
	    }
	    
	}

	/**
	 * the method getHashMap of class Transaction
	 * 
	 * 该方法加结果集中的一行数据存储到Hashtable类型的对象中，并返回这个Hashtable类型的对象
	 * 
	 * @param resultSet 结果集
	 * @param resultSetMetaData 结果集元数据
	 * @return Hashtable类型的对象
	 */
	public HashMap getHashMap(ResultSet resultSet,ResultSetMetaData resultSetMetaData){
		HashMap hashMap=null;
		try{
			int columnCount = resultSetMetaData.getColumnCount();
			hashMap = new HashMap();
		    for (int i = 1; i <= columnCount; i++) {
		        Object object = resultSet.getObject(i);
		        hashMap.put(resultSetMetaData.getColumnName(i),object);
		    }		    
		} catch (SQLException e) {   
			log.error("db qurey error:"+e.getMessage());   
	    }
		return hashMap;
	}
	
	/**
	 * the method close of class Transaction
	 * 
	 * 该方法用于关闭连接
	 * 
	 * 
	 */
	public void close(){
		if (statement != null) {   
            try {
            	statement.close();
			} catch (SQLException e) {
				log.error("db statement close error:"+e.getMessage());  
			}   
        } 
		if (connection != null) {   
            try {
				connection.close();
			} catch (SQLException e) {
				log.error("db connection close error:"+e.getMessage());  
			}   
        }else
        	log.info("db connection is closed.");
	}
	
	/**
	 * the method commit of class Transaction
	 * 
	 * 该方法用于提交当前连接上的操作，若提交失败，将回滚，最后关闭连接
	 * 
	 */
	public boolean commit(){
		try {
			if(flag==true){
				connection.commit();
				log.info("db commit completed.");
				log.info("db transaction end.");
				return flag;
			}else{
				log.error("db excute before commit error,rollback.");
				try {
					connection.rollback();
		        } catch (SQLException ex) {   
		        	log.error("db rollback error:"+ex.getMessage());   
			    } 
		        return flag;
			}
		} catch (SQLException e) {
			try {
				connection.rollback();
	        } catch (SQLException ey) {   
	        	log.error("db rollback error:"+ey.getMessage());   
		    }   
	        log.error("db commit error,rollback:"+e.getMessage());
	        return false;
		}finally{
			close();
			connection=null; 			
		}
	}

}
